package net.ojbk.jiaofu.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import net.ojbk.database.DatabaseConnection;
import net.ojbk.jiaofu.po.SyTaskSubmit;
import net.ojbk.po.UserData;

public class getUserDataDao {
	static final String tableName="userData";
	DatabaseConnection dbconn;
	Statement stmt;
	public getUserDataDao(DatabaseConnection dbconn) {
		this.dbconn = dbconn;
		stmt=dbconn.getStatement();
	}
	public UserData[] getUserData(String college,String grade,String clas) {
		ResultSet re;
		if(college!=null&&grade==null&&clas==null) {
			UserData uds[]=new UserData[getCountByCollege(college)];
			UserData ud;
			String sql="select * from "
					+tableName
					+" where college='"
					+ college
					+"';";
			System.out.print(sql);
			try {
				re = stmt.executeQuery(sql);
				for (int i=0; re.next(); i++) {
					ud=new UserData();
					// 赋值代码
					ud.setId(re.getString("id"));
					ud.setEmail(re.getString("email"));
					ud.setTel(re.getString("tel"));
					ud.setIdcard(re.getString("idcard"));
					ud.setGrade(re.getString("grade"));
					ud.setCollege(re.getString("college"));
					ud.setClas(re.getString("clas"));
					ud.setRegistrationTime(re.getString("registrationTime"));
					ud.setLastIP(re.getString("lastIP"));
					uds[i]=ud;
				}
				return uds;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} 
			
		}
		if(college!=null&&grade!=null&&clas==null) {
			UserData uds[]=new UserData[getCountByCollegeGrade(college,grade)];
			UserData ud;
			String sql="select * from "
					+tableName
					+" where college='"
					+ college
					+ "' and grade='"
					+ grade
					+ "';";
			try {
				re = stmt.executeQuery(sql);
				for (int i=0; re.next(); i++) {
					ud=new UserData();
					// 赋值代码
					ud.setId(re.getString("id"));
					ud.setEmail(re.getString("email"));
					ud.setTel(re.getString("tel"));
					ud.setIdcard(re.getString("idcard"));
					ud.setGrade(re.getString("grade"));
					ud.setCollege(re.getString("college"));
					ud.setClas(re.getString("clas"));
					ud.setRegistrationTime(re.getString("registrationTime"));
					ud.setLastIP(re.getString("lastIP"));
					uds[i]=ud;
				}
				return uds;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} 
		}
		if(college!=null&&grade!=null&&clas!=null) {
			UserData uds[]=new UserData[getCountByCollegeGradeClas(college,grade,clas)];
			UserData ud;
			String sql="select * from "
					+tableName
					+" where college='"
					+ college
					+ "' and grade='"
					+ grade
					+ "' and clas='"
					+ clas
					+ "';";
			try {
				re = stmt.executeQuery(sql);
				for (int i=0; re.next(); i++) {
					ud=new UserData();
					// 赋值代码
					ud.setId(re.getString("id"));
					ud.setEmail(re.getString("email"));
					ud.setTel(re.getString("tel"));
					ud.setIdcard(re.getString("idcard"));
					ud.setGrade(re.getString("grade"));
					ud.setCollege(re.getString("college"));
					ud.setClas(re.getString("clas"));
					ud.setRegistrationTime(re.getString("registrationTime"));
					ud.setLastIP(re.getString("lastIP"));
					uds[i]=ud;
				}
				return uds;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} 
		}
		return null;
	}
	public int getCountByCollege(String college) {    //yes
		int count = 0;
		ResultSet re;
		try {
			// SELECT COUNT(*) FROM userData
			String sql="SELECT COUNT(*) FROM "
					+ tableName
					+ " where college='"
					+ college
					+ "';";
			re = stmt.executeQuery(sql);
			if (re.next()) {
				count = re.getInt(1);
			}
		} catch(Exception e) {
			e.printStackTrace();
		}
		return count;
	}
	public int getCountByCollegeGrade(String college,String grade) {  //yes
		int count = 0;
		ResultSet re;
		try {
			// SELECT COUNT(*) FROM userData
			String sql="SELECT COUNT(*) FROM "
					+ tableName
					+ " where college='"
					+ college
					+ "' and grade= '"
					+ grade
					+ "';";
			re = stmt.executeQuery(sql);
			if (re.next()) {
				count = re.getInt(1);
			}
		} catch(Exception e) {
			e.printStackTrace();
		}
		return count;
	}
	public int getCountByCollegeGradeClas(String college,String grade,String clas) {  //yes
		int count = 0;
		ResultSet re;
		try {
			// SELECT COUNT(*) FROM userData
			String sql="SELECT COUNT(*) FROM "
					+ tableName
					+ " where college='"
					+ college
					+ "' and grade='"
					+ grade
					+ "' and clas='"
					+ clas
					+ "';";
			re = stmt.executeQuery(sql);
			if (re.next()) {
				count = re.getInt(1);
			}
		} catch(Exception e) {
			e.printStackTrace();
		}
		return count;
	}
	public static void main(String args[]) {
//		DatabaseConnection dbconn=new DatabaseConnection();
//		getUserDataDao gudd=new getUserDataDao(dbconn);
//		System.out.println(gudd.getCountByCollege("1"));
//		System.out.println(gudd.getCountByCollegeGrade("1", "1"));
//		System.out.println(gudd.getCountByCollegeGradeClas("1", "1", "1"));
//		UserData uds[];
//		uds=gudd.getUserData("1", "1", "1");
//		System.out.println(uds.length);
//		System.out.println(uds[1].getId());
	}
}
